package com.sam.excel;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @ProjectName: demo-excel
 * @Package: com.sam.excel
 * @ClassName: ExcelUtils
 * @Description:
 * @Author: Zhixin.Zhao
 * @CreateDate: 2018/9/13 10:26
 * @Version: 1.0
 * Copyright: Copyright (c) 2018
 */
public class ExcelUtils {

    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    public static List<ExcelBean> readExcel(String path) throws Exception {
        List<ExcelBean> list = new ArrayList<>();

        File file = new File(path);
        String fileName = file.getName();
        boolean notNull = false;
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new Exception("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = new FileInputStream(file);
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if (sheet != null) {
            notNull = true;
        }
        Row row = null;
        Map<String, Object> map = null;
        ExcelBean excelBean = null;
        //遍历当前sheet中的所有行
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            excelBean = new ExcelBean();
            if (row == null) {
                continue;
            } else {
                map = new LinkedHashMap<>();
            }
            for (int i = 0; i < row.getLastCellNum(); i++) {
                if (row.getCell(i) != null) {
                    Object value = getCellValue(row.getCell(i));
//                    map.put("str" + (i - 1), );
                    if(i==0 && value != null){
                        excelBean.setStr1(Integer.valueOf(value.toString()));
                    }
                    if(i==1 && value != null){
                        excelBean.setStr2(Integer.valueOf(value.toString()));
                    }
                    if(i==2 && value != null){
                        excelBean.setStr3(Double.valueOf(value.toString()));
                    }
                    if(i==3 && value != null){
                        excelBean.setStr4(Integer.valueOf(value.toString()));
                    }
                }
            }
//            BeanUtils.populate(excelBean, map);
            list.add(excelBean);
        }
        return list;
    }

    public static Object getCellValue(Cell cell) {
        String value = null;
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = String.valueOf(df.format(cell.getNumericCellValue()));
                } else {
                    value = String.valueOf(df2.format(cell.getNumericCellValue()));
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            case Cell.CELL_TYPE_FORMULA:
                try {
                    value = String.valueOf(df2.format(cell.getNumericCellValue()));
                } catch (IllegalStateException e) {
                    value = cell.getStringCellValue();
                }
                break;
            case Cell.CELL_TYPE_ERROR:
                value = "";
                break;
            default:
                value = "";
                break;
        }
        if (value.equals("") || value == null) {
            return null;
        }
        if (cell == null) {
            return null;
        }
        return value;
    }
}
